home
***
CD-ROM
|
disk
|
FTP
|
other
***
search
/
Software Vault: The Gold Collection
/
Software Vault - The Gold Collection (American Databankers) (1993).ISO
/
cdr11
/
pdox693.zip
/
TI163.ASC
< prev
next >
Wrap
Text File
|
1992-08-12
|
10KB
|
397 lines
PRODUCT : Paradox NUMBER : 163
VERSION : 2.0 & up
OS : DOS
DATE : August 12, 1992 PAGE : 1/6
TITLE : How to Create a Secondary Index
The purpose of this Technical Information Sheet is to provide an
explanation of how to establish secondary indexes on Paradox
tables in order to speed up certain operations. This document
will present alternative methods for the creation of secondary
indexes and the ways they can be used most effectively.
A secondary index is an index on any field in a table, whether it
is a primary key field or not. Paradox also calls a secondary
index a QUERY-SPEEDUP or a SPEEDUP FILE.
Secondary indexes are used to speed up the following Paradox
operations:
o Queries
o The Image | Zoom | Value command
o Zoom <Ctl-Z> and Zoom Next <Alt-Z>
o The PAL LOCATE command
CHARACTERISTICS OF SECONDARY INDEXES
o There can be MANY secondary indexes for a table: one for
each field.
o ANY field can have a secondary index.
o Secondary indexes have NO affect on the sort order of the
table.
o There is no uniqueness requirement for secondary index
values.
o Paradox DOES NOT support multi-field secondary indexes;
only single-field indexes.
HOW TO CREATE A SECONDARY INDEX
There are two ways to create a secondary index:
o Interactively using QuerySpeed
PRODUCT : Paradox NUMBER : 163
VERSION : 2.0 & up
OS : DOS
DATE : August 12, 1992 PAGE : 2/6
TITLE : How to Create a Secondary Index.
o Using the PAL INDEX command
INTERACTIVE METHOD
I. Set up a query for the table in question and enter a
criterion in the field you want to index on. As an
example; by entering the selection criterion of "Smith"
in the Last Name field of the query for the Customer
table.
II. Bring up the main menu by pressing <F-10> and select
Tools | Queryspeed. Paradox does not actually do the
query. Instead, it inspects the query and determines
if building an index would make the query faster. In
our example above, the program would create an index on
the Last Name field.
III. Shortcomings of the INTERACTIVE method:
A. Paradox decides whether a field merits an index.
B. For many queries, the Tools | QuerySpeed command
presents the message, No speedup possible.
PAL INDEX COMMAND METHOD
I. View the table in question and move the cursor to the
field that you want the index on.
II. Press the <Alt-F10> key. This will display what is
known as the "PAL Menu".
III. Select | Miniscript.
IV. Paradox will ask you to type in a command at the top of
the screen.
V. Type in the following line, exactly as seen here (upper
and lower case are not important), and press the
<Enter> key:
INDEX TABLE() ON FIELD()
PRODUCT : Paradox NUMBER : 163
VERSION : 2.0 & up
OS : DOS
DATE : August 12, 1992 PAGE : 3/6
TITLE : How to Create a Secondary Index.
VI. When you press the <Enter> key, your screen will not
change for some period of time while Paradox builds the
index. As long as your disk drive light is on, the
program is still working on it.
VII. Explanation of the PAL command:
INDEX -- the command to the program to build a
secondary index
file.
TABLE() -- means "the current table"
FIELD() -- means "the current field"
Alternative PAL command:
INDEX "Customer" on "Last name"
VIII. Be sure to spell correctly and put quotes around, the
table name and field name.
IX. If an index already exists, the INDEX command deletes
the old index and builds a new one, unconditionally.
MAINTAINED AND NON-MAINTAINED SECONDARY INDEXES
Secondary indexes come in two varieties: maintained and non-
maintained. The differences between the two is what happens when
editing the table.
With a MAINTAINED index:
Paradox immediately updates the index to reflect the change.
With a NON-MAINTAINED index:
The index is not updated.
Paradox marks it as "Not Current".
Paradox rebuilds it the next time it goes to use it. This
process can substantially slow down the above mentioned
PRODUCT : Paradox NUMBER : 163
VERSION : 2.0 & up
OS : DOS
DATE : August 12, 1992 PAGE : 4/6
TITLE : How to Create a Secondary Index.
operations. With this in mind, MAINTAINED indexes are almost
always better.
HOW TO CREATE A MAINTAINED SECONDARY INDEX
THE PAL INDEX COMMAND METHOD
I. Use the same instructions as earlier, but add the word
MAINTAINED after the word INDEX:
INDEX MAINTAINED TABLE() ON FIELD()
or
INDEX MAINTAINED "CUSTOMER" ON "LAST NAME"
THE QUERYSPEED METHOD:
I. No direct way to specify "maintained" or non-
maintained.
II. In the Custom Configuration Program, a global
preference can be set. Use the menu option PAL |
MaintainedIndexes in the
Custom Script Menu.
-- set to YES for maintained.
-- set to NO for non-maintained.
III. Note: The use of the MaintainIndexes selection in the
PAL menu of the Custom Script is a limited option. In
fact, it has nothing to do with PAL. This function
does not have any effect on the PAL INDEX command. It
only effects indexes created interactively with the
Tools | Queryspeed menu choice.
MAINTAINED INDEXES AND EDITING TABLES
If changes are made to a table in EDIT mode using the <F9> key:
I. Paradox delays updating the index until the <F2> key is
pressed.
PRODUCT : Paradox NUMBER : 163
VERSION : 2.0 & up
OS : DOS
DATE : August 12, 1992 PAGE : 5/6
TITLE : How to Create a Secondary Index.
II. Meanwhile, Paradox marks the index as "temporarily not
current."
III. If Zoom (<CTL-Z> is used on a field, Paradox will not
use the index and the search will be slow.
If changes are made while in COEDIT mode using the <ALT-F9 key>:
I. Paradox keeps all the maintained indexes up-to-date
after each record.
II. Zooms on indexed fields will continue to be fast.
III. In general terms; it is better to use COEDIT mode when
working with indexed tables although the ability to use
full incremental undos is lost. While in COEDIT mode
UNDO <CTL-U> will only undo the most recent change
made.
PATTERN SEARCHES
I. For queries, Paradox does not use an index to enhance
the performance of a criteria with a pattern.
II. For zooms (and LOCATE PATTERN), a pattern is used only
if the string does not begin with a pattern e.g.
"smi.." will be fast.
"..ith" will not be.
III. A pattern search is not case sensitive.
"smi.." will match smi, SMI, Smi, sMI, etc.
IV. Paradox uses the index to match only the first letter
of the pattern, It then performs a sequential search
from there. If many values start with the same letter,
an index will not help pattern zooms.
HOW TO IDENTIFY INDEX FILES
File name extensions
PRODUCT : Paradox NUMBER : 163
VERSION : 2.0 & up
OS : DOS
DATE : August 12, 1992 PAGE : 6/6
TITLE : How to Create a Secondary Index.
.DB -- the table's data
.PX -- the table's primary index
.Xnn or .Ynn -- secondary index
(The "nn" will be a hexadecimal number from 01 through FF.)
ADDITIONAL INFORMATION CONCERNING SECONDARY INDEXES
o Indexing tables, pages 183-185 and pages 296-298 in the
PAL User's Guide (3.5).
o Indexing tables, pages 277-278 in the Paradox User's Guide
(3.5).
o In the PAL User's Guide and Paradox User's Guide, look in
the index under "Index" for more information.
(This Technical Information Sheet is based on material by Brian
J. Smith and Associates Inc.)
DISCLAIMER: You have the right to use this technical information
subject to the terms of the No-Nonsense License Statement that
you received with the Borland product to which this information
pertains.